Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Mastering SQL querying is not an easy task, but with the proper mind set, it is intuitive and efficient, thanks to the relational model upon which SQL is based.

The syntax of the SELECT statement is shown here:

SELECT column_names
FROM table_names
WHERE predicates

Let’s take a look at the various functions of the SELECT command. To retrieve a complete table, run this query:

SELECT * FROM EMPLOYEE;

To get a list of employees in the Editorial department, run this query:

SELECT * FROM EMPLOYEE
WHERE department = 'Editorial';

To sort the list based on the employees’ last names, use the ORDER BY directive:

SELECT * FROM EMPLOYEE
WHERE department= 'Editorial'
ORDER BY lastname;

To get this ordered list but only see the employee number, enter the following statements:

SELECT empno FROM EMPLOYEE
WHERE department = 'Editorial'
ORDER BY lastname;

To get a list of users with the name Pratik Patel, you would enter:

SELECT * FROM EMPLOYEE
WHERE (firstname='Pratik') AND (lastname='Patel');

What if we want to show two tables at once? No problem, as shown here:

SELECT EMPLOYEE.*, CONFIDENTIAL.*
FROM EMPLOYEE, CONFIDENTIAL;

Here’s a more challenging query: Show the salary for employees in the Editorial department. According to our tables, the salary information is in the CONFIDENTIAL table, and the department in which an employee belongs is in the EMPLOYEE table. How do we associate a comparison in one table to another? Since we used the reference of the employee number in the CONFIDENTIAL table from the EMPLOYEE table, we can specify the employees that match a specified department, and then use the resulting employee number to retrieve the salary information from the CONFIDENTIAL table:

SELECT c.salary
FROM EMPLOYEE as e, CONFIDENTIAL as c
WHERE e.department = 'Editorial'
           AND c.empno = e.empno;

We have declared something like a variable using the as keyword. We can now reference the specific fields in the table using a “.”, just like an object. Let’s begin by determining which people in the entire company are making more than $25,000:

SELECT salary
FROM CONFIDENTIAL
WHERE salary > 25000;

Now let’s see who in the Editorial department is making more than $25,000:

SELECT c.salary
FROM EMPLOYEE as e, CONFIDENTIAL as c
WHERE e.department = 'Editorial'
           AND c.empno = e.empno
           AND c.salary > 25000;

You can perform a number of other functions in SQL, including averages. Here’s how to get the average salary of the people in the Editorial department:

SELECT AVG (c.salary)
FROM EMPLOYEE as e, CONFIDENTIAL as c
WHERE e.department = 'Editorial'
           AND c.empno = e.empno;

Of course, the possibilities with SQL exceed the relatively few examples shown in this chapter. Because this book’s goal is to introduce the JDBC specifically, I didn’t use complex queries in the examples. And now our discussion on SQL is complete. If you are interested in learning more about SQL, I recommend that you check out our book’s Website, where I have posted a list of recommended books on the topic of SQL and distributed databases.

Coming Up Next

The next chapter begins our journey into JDBC. I’ll show you how to use JDBC drivers for connecting to data sources. Then we’ll cover installing drivers, as well as the proper way to use drivers that are dynamically fetched with an applet. Finally, we’ll discuss the security restrictions of using directly downloaded drivers as opposed to locally installed drivers.


Previous Table of Contents Next